/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Metodos;

import Atributos.Curso;
import Atributos.Disciplina;
import BD.CriaConexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Andre
 */
public class DisciplinaMet {
    private Connection conexao;
    public DisciplinaMet(){
        try {
            this.conexao=(Connection) CriaConexao.getConexao();
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void AdicionaDisciplina(Disciplina d, Curso c) throws SQLException {
       
        String sql_a = "insert into disciplina (nome_disc, eixo) values (?,?)";
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql_a)) {
            stmt.setString(1, d.getNome());
            stmt.setString(2, d.getEixo());
            
            stmt.execute();
        }
        if("Profissional".equals(d.getEixo())){
        String sql3 = "select * from disciplina where nome_disc like ?";
            int id=0;
            try (PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql3)) {
                stmt3.setString(1, d.getNome());
                try (ResultSet rs3 = stmt3.executeQuery()) {
                    while (rs3.next()){                   
                        d.setId_disc(rs3.getInt("id_disc"));
                        id=d.getId_disc();     
                    }
                }
            }
        String sql="insert into disciplina_curso(id_disciplina_dc, id_curso_dc)"+"values(?,?)";
            try (PreparedStatement consulta = (PreparedStatement) conexao.prepareStatement(sql)) {
                consulta.setInt(1, d.getId_disc());
                consulta.setInt(2, c.getId_curso());
                
                consulta.execute();
            }
        }
    }
    
     public void AlteraDisciplina(Disciplina d) throws SQLException {
        String eixo = null;
        String sql = " update disciplina set nome_disc=?,eixo=? where id_disc=?";
        String sql1="select disciplina.eixo from disciplina where disciplina.id_disc=?";
        String sql2 = " update disciplina set nome_disc=?,eixo=? where  disciplina.id_disc=?";
        String sql3="insert into disciplina_curso (id_disciplina_dc, id_curso_dc) values (?,?)";
        String sql4="delete from disciplina_curso where id_disciplina_dc=?";
        String sql5="update disciplina_curso set id_curso_dc=?  where  disciplina_curso.id_disciplina_dc=?";
        
        PreparedStatement stmt1 = (PreparedStatement) conexao.prepareStatement(sql1);
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        PreparedStatement stmt2 = (PreparedStatement) conexao.prepareStatement(sql2);
        PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql3);
        PreparedStatement stmt4 = (PreparedStatement) conexao.prepareStatement(sql4);
        PreparedStatement stmt5 = (PreparedStatement) conexao.prepareStatement(sql5);
        if("Regular".equals(d.getEixo())){
            stmt1.setInt(1, d.getId_disc());
            ResultSet rs = stmt1.executeQuery();
             while (rs.next()) {
                eixo=rs.getString("eixo");
        }
            if("Regular".equals(eixo)){
        stmt.setString(1, d.getNome());
        stmt.setString(2, d.getEixo());
        stmt.setInt(3, d.getId_disc());
        stmt.execute();
        stmt.close();
            System.out.println("de regular para regular");
            }else{
        stmt.setString(1, d.getNome());
        stmt.setString(2, d.getEixo());
        stmt.setInt(3, d.getId_disc());
        stmt.execute();
        stmt.close();        
        
        stmt4.setInt(1, d.getId_disc());
        stmt4.execute();
        stmt4.close();        
                System.out.println("de profissional para regular"); 
            }
        }else{
            stmt1.setInt(1, d.getId_disc());
            try (ResultSet rs = stmt1.executeQuery()) {
                while (rs.next()) {
                   eixo=rs.getString("eixo");
           }
               if("Regular".equals(eixo)){
           stmt.setString(1, d.getNome());
           stmt.setString(2, d.getEixo());
           stmt.setInt(3, d.getId_disc());
           stmt.execute();
           stmt.close();
           stmt3.setInt(1, d.getId_disc());
           stmt3.setInt(2, d.getId_curso());
           stmt3.execute();
           stmt3.close();
            System.out.println("de regular para profissional");
               }else{
           stmt2.setString(1, d.getNome());
           stmt2.setString(2, d.getEixo());
           
           stmt2.setInt(3, d.getId_disc());
           stmt2.execute();
           stmt2.close();
           
           
           stmt5.setInt(1, d.getId_curso());
           stmt5.setInt(2, d.getId_disc());
           stmt5.execute();
           stmt5.close();
            System.out.println("de profissional para profissional");
               }
            }
        stmt1.close();
        }

    }
    
    public void ExcluiDisciplina(Disciplina get) throws SQLException {
        String sql = "delete from disciplina where id_disc=?";
        String sql2 = "delete from prof_disc where  id_disc_pd=?";
        String sql3 = "delete from disciplina_curso where id_disciplina_dc=?";
        String sql4 = "delete from hora_aula where id_disc_ha=?";

if(HaPD(get)){
            try (PreparedStatement stmt2 = (PreparedStatement) conexao.prepareStatement(sql2)) {
                stmt2.setInt(1, get.getId_disc());
                stmt2.execute();
            }
}

if(HaDC(get)){
            try (PreparedStatement stmt3 = (PreparedStatement) conexao.prepareStatement(sql3)) {
                stmt3.setInt(1, get.getId_disc());
                stmt3.execute();
            }
}
if(HaHA(get)){
            try (PreparedStatement stmt4 = (PreparedStatement) conexao.prepareStatement(sql4)) {
                stmt4.setInt(1, get.getId_disc());
                stmt4.execute();
            }
}
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setInt(1, get.getId_disc());
            stmt.execute();
        }
    }
    
    public List<Disciplina> BuscarDisciplinas(String nome) throws SQLException {
        String sql = "select * from disciplina where  disciplina.nome_disc like ? order by disciplina.eixo";
        String sql2 = "select curso.nome_curso,curso.id_curso from curso,disciplina,disciplina_curso where disciplina.id_disc=disciplina_curso.id_disciplina_dc and disciplina_curso.id_curso_dc=curso.id_curso  and disciplina.id_disc = ? order by curso.nome_curso";
        List<Disciplina> minhaLista;
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            PreparedStatement stmt2 = (PreparedStatement) conexao.prepareStatement(sql2);
            stmt.setString(1, nome);
            try (ResultSet rs = stmt.executeQuery()) {
                minhaLista = new ArrayList<>();
                while (rs.next()) {
                    Disciplina a1 = new Disciplina();
                    a1.setId_disc(rs.getInt("id_disc"));
                    a1.setNome(rs.getString("nome_disc"));
                    a1.setEixo(rs.getString("eixo"));
                    if("Profissional".equals(a1.getEixo())){
                stmt2.setInt(1, a1.getId_disc());
                ResultSet rs2 = stmt2.executeQuery();
                
                while (rs2.next()) {
                   
                a1.setNomecurso(rs2.getString("nome_curso"));
                a1.setId_curso(rs2.getInt("id_curso"));
                
                }
                    }
                    minhaLista.add(a1);
                }
            }
        }
        return minhaLista;
    }
    
   public boolean BuscaDisciplinaRegseiguais(Disciplina d) {
        String sql = "select disciplina.nome_disc, disciplina.eixo from disciplina where  disciplina.nome_disc=? and disciplina.eixo=?";
        int decisao=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1,d.getNome());
                stmt.setString(2,d.getEixo());
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        if(rs.getString("nome_disc").equals(d.getNome()) && rs.getString("eixo").equals(d.getEixo())){
                         decisao=1;   
                        }            
                        
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                return false;
            }else{
               return true; 
            }
    }
   
   public boolean BuscaDisciplinaProfseiguais(Disciplina d,Curso c) {
       int decisao=0;
     
           
          String sql = "select disciplina.nome_disc, disciplina.eixo from disciplina,curso,disciplina_curso where  disciplina.nome_disc=? and disciplina.eixo=? and disciplina.id_disc=disciplina_curso.id_disciplina_dc and disciplina_curso.id_curso_dc=curso.id_curso and curso.id_curso=?";
        
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1,d.getNome());
                stmt.setString(2,d.getEixo());
                stmt.setInt(3,c.getId_curso());
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        if(rs.getString("nome_disc").equals(d.getNome()) && rs.getString("eixo").equals(d.getEixo())){
                         decisao=1;   
                        }            
                        
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaMet.class.getName()).log(Level.SEVERE, null, ex);
        } 
       
            if(decisao==1){
                return false;
            }else{
               return true; 
            }
    }
   
    public List<Disciplina> BuscarDisciplinaCurso(String curso) throws SQLException {
        String sql = "select disciplina.nome_disc from disciplina,disciplina_curso,curso where disciplina_curso.id_disciplina_dc=disciplina.id_disc"
                + " and disciplina_curso.id_curso_dc=curso.id_curso  and curso.nome_curso=? ";
            List<Disciplina> lista = new ArrayList<>();
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setString(1, curso);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Disciplina c = new Disciplina();

                    c.setNome(rs.getString("nome_disc"));
                    
                    lista.add(c);
                    
                }
            }
        }
           
        
          return lista;
    }

    private boolean HaDC(Disciplina get) {
            String sql = "select id_disciplina_dc from disciplina_curso where id_disciplina_dc=? ";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, get.getId_disc());
            
                    ResultSet rs = stmt.executeQuery();
       
                    while (rs.next()){
                    
                     decisao++;  
                        
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>=1){
                System.out.println("nao encontrou");
                return true;
                
            }else{
                System.out.println("encontrou");
               return false; 
            } 
    }

    private boolean HaPD(Disciplina get) {
           String sql = "select id_disc_pd from prof_disc where id_disc_pd=? ";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, get.getId_disc());
            
                    ResultSet rs = stmt.executeQuery();
       
                    while (rs.next()){
                    
                     decisao++;  
                        
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>=1){
                System.out.println("nao encontrou");
                return true;
                
            }else{
                System.out.println("encontrou");
               return false; 
            } 
    }

    private boolean HaHA(Disciplina get) {
          String sql = "select id_disc_ha from hora_aula where id_disc_ha=? ";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, get.getId_disc());
            
                    ResultSet rs = stmt.executeQuery();
       
                    while (rs.next()){
                    
                     decisao++;  
                        
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>=1){
                System.out.println("nao encontrou");
                return true;
                
            }else{
                System.out.println("encontrou");
               return false; 
            } 
    }
    
    
    public List<Disciplina> Buscar_Disc_Regular(String a) throws SQLException {
        String sql = "select * from disciplina where eixo = ?";
            List<Disciplina> lista = new ArrayList<>();
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setString(1, a);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Disciplina c = new Disciplina();

                    c.setNome(rs.getString("nome_disc"));
                    
                    lista.add(c);
                    
                }
            }
        }
           
        
          return lista;
    }
    
    
    public List<Disciplina> Buscar_Nome_DisciplinasProf(String asd, String asd2) {
        String sql = "select disciplina.nome_disc from disciplina_curso,curso,disciplina where disciplina.id_disc= disciplina_curso.id_disciplina_dc and disciplina_curso.id_curso_dc=curso.id_curso and curso.nome_curso like ?";
             List<Disciplina> lista = new ArrayList<>();
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, asd);
                 System.out.println("asd "+asd);
//                stmt.setString(2, asd);
//                 System.out.println("asd "+asd);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        Disciplina c = new Disciplina();

                        c.setNome(rs.getString("nome_disc"));
                        System.out.println("materia "+c.getNome());
                       
                        lista.add(c);
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
          return lista;
    }
    
    
     public int BuscaIDDisciplina(Disciplina d) {
         String sql = "select disciplina.id_disc from disciplina where disciplina.nome_disc like ? ";
            int id=0;
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, d.getNome());
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                       
                    id=rs.getInt("id_disc");  
                        
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }

    public List<Disciplina> BuscaTodasDiscProf(String a) throws SQLException {
         String sql = "select disciplina.nome_disc from disciplina,disciplina_curso,curso where disciplina_curso.id_disciplina_dc=disciplina.id_disc and disciplina_curso.id_curso_dc=curso.id_curso and disciplina.nome_disc like ? order by curso.nome_curso,disciplina.nome_disc";
            List<Disciplina> lista = new ArrayList<>();
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setString(1, a);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Disciplina c = new Disciplina();

                    c.setNome(rs.getString("nome_disc"));
                    
                    lista.add(c);
                    
                }
            }
        }
           
        
          return lista;
    }
    
    public List<Disciplina> BuscaTodasDiscProfReg(String a) throws SQLException {
         String sql = "select nome_disc from disciplina where eixo like 'Regular' and nome_disc like ? order by nome_disc";
            List<Disciplina> lista = new ArrayList<>();
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setString(1, a);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Disciplina c = new Disciplina();

                    c.setNome(rs.getString("nome_disc"));
                    
                    lista.add(c);
                    
                }
            }
        }
           
        
          return lista;
    }

    public boolean VerificaseProfisional(String disciplina) {
         String sql = "select disciplina.eixo from disciplina where disciplina.nome_disc=?";
        int decisao=0;
        try {
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setString(1, disciplina);
            
                    ResultSet rs = stmt.executeQuery();
       
                    while (rs.next()){
                    if(rs.getString("eixo").equals("Profissional")){
                     decisao++;  
                    }
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao>=1){
                
                return true;
                
            }else{
                
               return false; 
            } 
    }

    public boolean BuscaSEHADiscReg() {
        String sql = "select nome_disc from disciplina where eixo like 'Regular'";
        int decisao=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql); ResultSet rs = stmt.executeQuery()) {

                
                   
                    if(!rs.next()){
                     decisao=1;  
                        
                    }            
                    
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
               
                return false;
                
            }else{
                
               return true; 
            }
    }
}
